﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;


namespace XinYiOffice.Common
{
    public static class DBTools
    {
        /// <summary>
        /// 根据条件将行转成列
        /// </summary>
        /// <param name="dt">原datatable,一般是重复行</param>
        /// <param name="rowtocolumns">配置要将行转换为列的字符串如 ItemName:ValueDate| 说明: 作为新列名的列:新值的列|新列名2:新值</param>
        /// <returns></returns>
        public static DataTable ConvertRowToColumns(DataTable dt, string rowtocolumns)
        {
            //1,将重复的id数据，应该都转换成一行
            //2,将重复id的数据，后面所对应的列作为 新列的列名 及 列值

            Dictionary<string, string> dicRowtoColumns = new Dictionary<string, string>();//需要准备转换的同一行的2个列,一个作为新的列名，一个作为列名所对应的值
            DataTable dtNew = dt.Clone();//复制新结构
            dtNew.TableName = "t";

            //记录 新列名及新值的 键值序列
            if (!string.IsNullOrEmpty(rowtocolumns) && rowtocolumns.Split('|').Length > 0)
            {
                string[] _item = rowtocolumns.Split('|');
                foreach (string str in _item)
                {
                    string[] keyarry = str.Split(':');
                    if (keyarry.Length <= 1)
                    {
                        continue;
                    }

                    string _column = keyarry[0];//变成列名
                    string _value = keyarry[1];//变成列对应的值

                    if (!dicRowtoColumns.ContainsKey(_column))
                    {
                        dicRowtoColumns.Add(_column, _value);
                    }
                }
            }

          
            //将原有第一个数据填充
            Dictionary<string, string> dicNewsRow = new Dictionary<string, string>();//记录新的行
            DataRow _dr = null;

            foreach (DataRow dr in dt.Rows)
            {
                if (!dicNewsRow.ContainsKey(dr[0].ToString()))
                {
                    _dr = dtNew.NewRow();//只有id不存在 才新表中创建一行,之后的循环都为填充
                }

                for (int i = 0; i < dr.ItemArray.Length; i++)
                {
                    //当前列名存在于行转列序列中，则添加此行数据值
                    if (dicRowtoColumns.ContainsKey(dr.Table.Columns[i].ColumnName))
                    {
                        if (!dtNew.Columns.Contains(dr[i].ToString()))//值若不存在新表中,就添加此列
                        {
                            dtNew.Columns.Add(dr[i].ToString());//把当前的值作为新列名
                        }
                    }

                    //查找为新列中作为值的 列 
                    #region 查询出要做值的那一列,赋给做新列名
                    bool isValue = false;
                    foreach (KeyValuePair<string, string> kp in dicRowtoColumns)
                    {
                        if (dr.Table.Columns[i].ColumnName == kp.Value)
                        {
                            string _keycolumn = dr[kp.Key].ToString();//符合当前值的key作为新的列名

                            _dr[_keycolumn] = dr[i];
                            isValue = true;
                        }
                    }
                    #endregion

                    if (!isValue)
                    {
                        _dr[i] = dr[i];
                    }

                }

                if (!dicNewsRow.ContainsKey(dr[0].ToString()))
                {
                    dicNewsRow.Add(dr[0].ToString(), dr[0].ToString());//记录已经保存了 此id 数据
                    dtNew.Rows.Add(_dr);
                }
            }

            return dtNew;
        }

        /// <summary>
        /// 将DataTable进行分页并生成新的DataTable
        /// </summary>
        /// <param name="dt">原DataTable</param>
        /// <param name="PageIndex">需要的第n页</param>
        /// <param name="PageSize">每页页数</param>
        /// <returns>新的DataTable</returns>
        public static DataTable GetPagedTable(DataTable dt, int PageIndex, int PageSize)
        {
            if (PageIndex == 0)
            {
                return dt;
            }
            DataTable newdt = dt.Copy();
            newdt.Clear();
            //起始行数
            int rowbegin = (PageIndex - 1) * PageSize;
            //结束行数
            int rowend = PageIndex * PageSize;
            if (rowbegin >= dt.Rows.Count)
            {
                return newdt;
            }

            if (rowend > dt.Rows.Count)
            {
                rowend = dt.Rows.Count;
            }
            //生成新的DataTable
            for (int i = rowbegin; i <= rowend - 1; i++)
            {
                DataRow newdr = newdt.NewRow();
                DataRow dr = dt.Rows[i];
                foreach (DataColumn column in dt.Columns)
                {
                    newdr[column.ColumnName] = dr[column.ColumnName];
                }
                newdt.Rows.Add(newdr);
            }

            return newdt;
        }

        /// <summary>
        /// 获取Dataset前几条数据的两种方法
        /// </summary>
        /// <param name="Top"></param>
        /// <param name="oDT"></param>
        /// <returns></returns>
        public static DataTable SelectTop(int Top, DataTable oDT)
        {
            if (oDT.Rows.Count < Top) return oDT;

            DataTable NewTable = oDT.Clone();
            DataRow[] rows = oDT.Select("1=1");
            for (int i = 0; i < Top; i++)
            {
                NewTable.ImportRow((DataRow)rows[i]);
            }
            return NewTable;
        }

        public static int GetConentCount(string tabName, string whEre)
        {
            int id = 0;

            DataSet ds = DbHelperSQL.Query(string.Format("select count(1) from {0} where {1}", SelTable(tabName), whEre));
            if (ds.Tables.Count > 0)
            {
                id = int.Parse(ds.Tables[0].Rows[0][0].ToString());
            }

            return id;
        }

        public static int GetCountDistinct(string tableName,string whEre)
        {
            int id=0;
            string mysql="SELECT COUNT(DISTINCT ContentId) AS c ";
            mysql+="FROM  {0} ";
            mysql+="WHERE {1}";

            DataSet ds=DbHelperSQL.Query(string.Format(mysql,SelTable(tableName),whEre));

            if(ds.Tables.Count>0)
            {
                id=int.Parse(ds.Tables[0].Rows[0][0].ToString());
            }

            return id;
        }


        /// <summary>
        /// 多个TableName时,默认链接应该读第一个表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string SelTable(string tableName)
        {
            string _str = string.Empty;


            if (!string.IsNullOrEmpty(tableName))
            {
                string[] _table = tableName.Split(',');
                if (_table != null && _table.Length >= 1)
                {
                    _str = _table[0].ToString();
                }
                else
                {
                    _str = tableName;
                }
            }

            return _str;

        }
    }
}
